/*
 * StatementUtil.java
 *
 * Created on September 27, 2002, 4:35 PM
 */

package com.myebills.ebpsfrontend.cust.utils;

import com.myebills.ebpsfrontend.cust.controller.*;
import com.myebills.ebpsfrontend.cust.maxis.*;
import com.checkfree.isolutions.customer.persist.Account;
import com.checkfree.isolutions.statement.persist.Statement;
import com.checkfree.isolutions.statement.StatementConstants;
import com.checkfree.isolutions.statement.*;
import com.checkfree.isolutions.admin.*;
import com.checkfree.isolutions.streaming.*;
import com.checkfree.isolutions.property.Properties;
import com.checkfree.isolutions.property.PropertiesConfig;
import java.util.*;
import java.text.*;
import java.sql.*;
import java.util.Date;


/**
 *
 * @author  Lendy
 * @version
 */
public class StatementUtil {

    /** Creates new StatementUtil */
    public StatementUtil() {
    }

/**********************************************************************************************/

    public static Connection getDbConnection() throws Exception {

        Connection conn=null;

        try {

			Properties appCfg = PropertiesConfig.getApplicationSettings();

			String jdbcDriver = new String();
			String jdbcDataSource = new String();
			String username = new String();
			String password = new String();

			if (appCfg.hasProperty("JDBCDriverName") == false)
				throw new IllegalArgumentException("JDBC Driver not specified!");
			else
				jdbcDriver = appCfg.getProperty("JDBCDriverName");

			if (appCfg.hasProperty("JDBCDataSourceName") == false)
				throw new IllegalArgumentException("JDBC Data Source not specified!");
			else
				jdbcDataSource = appCfg.getProperty("JDBCDataSourceName");

			if (appCfg.hasProperty("DataBaseUserid") == false)
				throw new IllegalArgumentException("Database userid not specified!");
			else
				username = appCfg.getProperty("DataBaseUserid");

			if (appCfg.hasProperty("DataBasePassword") == false)
				throw new IllegalArgumentException("Database password not specified!");
			else
				password = appCfg.getProperty("DataBasePassword");

			Class.forName(jdbcDriver);
			conn = DriverManager.getConnection(jdbcDataSource, username, password);
			System.out.println("--- Connection opened ---");
        }
        catch(Exception e) {
           	throw new Exception("Error getting DB connection: " + e);
        }

        return conn;
    }

    public static void freeDbConnection(Connection conn) throws Exception {
        try {
			if (conn != null){
				conn.close();
				System.out.println("--- Connection closed ---");
			} else {
				System.out.println("--- Connection is null ---");
			}
        }
        catch(Exception e) {
            throw new Exception("Error closing DB connection : " + e);
        }
   }
/************************************************************************************************/

    // return vector of statements for the last X months for the given account
    public static Statement getStatement(Account account, int STMT_START_MONTH, int index) {

        Vector vStatements = getStatements(account, STMT_START_MONTH);
        System.out.println("vStatements.size() is "+vStatements.size());
        if (vStatements.size()>0){
            return (Statement) vStatements.elementAt(index);
        } else {
            return null;
        }
    }

    // return vector of statements for the last X months for the given account
    public static CorpStatement getCorpStatement(Account account, int STMT_START_MONTH, int index) {
        Vector vStatements = getCorpStatements(account, STMT_START_MONTH);
        if (vStatements.size()>0){
            return (CorpStatement) vStatements.elementAt(index);
        } else {
            return null;
        }
    }

    // return vector of statements for the last X months for the given account
    public static Vector getStatements(Account account, int STMT_START_MONTH) {

		System.out.println("getStatements ...1");

        TimeZone tz = TimeZone.getTimeZone(EbppxConstants.TIMEZONE);
        Calendar cal = Calendar.getInstance(tz);
        Date endDate = cal.getTime();
        //Get the start Date
        cal.add(Calendar.MONTH, STMT_START_MONTH);
        cal.set(Calendar.DATE, 1);
        cal.set(Calendar.HOUR_OF_DAY, 0);
        cal.set(Calendar.MINUTE, 0);
        cal.set(Calendar.SECOND, 0);
        cal.set(Calendar.MILLISECOND, 0);
        Date startDate = cal.getTime();

        Vector vStatements = StatementUtil.getStatementsByDateRange(account, startDate, endDate);

        return vStatements;

    }

    // return vector of statements for the last X months for the given account
    public static Vector getCorpStatements(Account account, int STMT_START_MONTH) {

        TimeZone tz = TimeZone.getTimeZone(EbppxConstants.TIMEZONE);
        Calendar cal = Calendar.getInstance(tz);
        Date endDate = cal.getTime();
        //Get the start Date
        cal.add(Calendar.MONTH, STMT_START_MONTH);
        cal.set(Calendar.DATE, 1);
        cal.set(Calendar.HOUR_OF_DAY, 0);
        cal.set(Calendar.MINUTE, 0);
        cal.set(Calendar.SECOND, 0);
        cal.set(Calendar.MILLISECOND, 0);
        Date startDate = cal.getTime();

        Vector vStatements = StatementUtil.getCorpStatementsByDateRange(account, startDate, endDate);

        return vStatements;

    }

    /** Retrieves the account's statements by date range*/
    public static Vector getStatementsByDateRange(Account cfiAccount, Date startDate, Date endDate){
        Vector cfiStmts = new Vector();
        Connection connection = null;
        PreparedStatement pstmt = null;
        ResultSet rs= null;
        StringBuffer query = new StringBuffer();

        try {
			connection = getDbConnection();
            if (cfiAccount!=null){

	            if (connection!=null){

					long tStartDate = com.checkfree.isolutions.utility.DateUtility.asJulian(startDate);
					long tEndDate = com.checkfree.isolutions.utility.DateUtility.asJulian(endDate);

					//AcctDetails acctDetails = new AcctDetails();
					//String acctId = acctDetails.getAcctId(cfiAccount.getExternalKey());
					//String acctKey = acctDetails.getAcctKey(cfiAccount.getExternalKey());

					query.append("select s.instanceid from statement s, account a ");
					query.append("where s.accountid = a.id ");
					query.append("and a.externalaccountkey = ?  ");
					//query.append("or a.externalaccountkey = ? ");
					//query.append("or a.externalaccountkey = ?) ");
					query.append("and s.statementdate between ? and ? ");
					query.append("order by statementdate desc ");


					// prepare the statement and bind parameter
					pstmt = connection.prepareStatement(query.toString());
					/*pstmt.setString(1,acctId);
					pstmt.setString(2,acctKey);
					pstmt.setString(3,cfiAccount.getExternalKey());
					pstmt.setLong(4,tStartDate);
					pstmt.setLong(5,tEndDate);*/

					pstmt.setString(1,cfiAccount.getExternalKey());
					pstmt.setLong(2,tStartDate);
					pstmt.setLong(3,tEndDate);

					System.out.println(query.toString());

					System.out.println("####### cfiAccount.getExternalKey() = "+ cfiAccount.getExternalKey());
					System.out.println("####### tStartDate = " + tStartDate);
					System.out.println("####### tEndDate = " + tEndDate);

					rs   = pstmt.executeQuery();

					com.checkfree.isolutions.statement.persist.StatementHome home = com.checkfree.isolutions.statement.persist.StatementHomeImpl.singleton();

					while(rs.next()){
						int instanceid = rs.getInt(1);
						com.checkfree.isolutions.admin.ObjectId objectId = new com.checkfree.isolutions.admin.ObjectId(com.checkfree.isolutions.streaming.IClassId.STATEMENT, instanceid);
						com.checkfree.isolutions.statement.persist.Statement cfiStatement = home.findById(objectId);
						if (cfiStatement != null) {
							cfiStmts.addElement(cfiStatement);
						}
					}

					if (pstmt != null) {
						pstmt.close();
					}
					if(rs!=null){
						rs=null;
					}
				}
            }
        }
        catch (Exception ex) {
            System.out.print("-- EXCEPTION: " + ex.getClass() + " " + ex.getMessage());
        }
        catch (Throwable t) {
            System.out.print("-- EXCEPTION: " + t.getClass() + " " + t.getMessage());
        }
        finally {
			if(connection!=null){
				try {
            	    freeDbConnection(connection);
				}
				catch (Exception dbe) {
					System.out.println("Error : " + dbe);
				}
            }
			if(rs!=null){
				rs=null;
			}
        }
        return cfiStmts;
    }

    /** Retrieves the account's statements by date range*/
    public static Vector getCorpStatementsByDateRange(Account cfiAccount, Date startDate, Date endDate){
        Vector cfiStmts = new Vector();
        Connection connection = null;
        PreparedStatement pstmt = null;
        ResultSet rs= null;
        StringBuffer query = new StringBuffer();

        try {
			connection = getDbConnection();
            if (cfiAccount!=null){

	            if (connection!=null){

					long tStartDate = com.checkfree.isolutions.utility.DateUtility.asJulian(startDate);
					long tEndDate = com.checkfree.isolutions.utility.DateUtility.asJulian(endDate);

					AcctDetails acctDetails = new AcctDetails();
					String acctId = acctDetails.getAcctId(cfiAccount.getExternalKey());
					String acctKey = acctDetails.getAcctKey(cfiAccount.getExternalKey());

					query.append("select s.instanceid,to_char(to_date(s.statementdate,'J'),'dd/mm/yyyy'),s.userfield2,s.userfield3,s.userfield4,ckfrconvert1.to_instance(s.statementrunid) from statement s, account a ");
					query.append("where s.accountid = a.id ");
					query.append("and (a.externalaccountkey = ? ");
					query.append("or a.externalaccountkey = ? ");
					query.append("or a.externalaccountkey = ?) ");
					query.append("and s.statementdate between ? and ? ");
					query.append("order by statementdate desc ");


					// prepare the statement and bind parameter
					pstmt = connection.prepareStatement(query.toString());
					pstmt.setString(1,acctId);
					pstmt.setString(2,acctKey);
					pstmt.setString(3,cfiAccount.getExternalKey());
					pstmt.setLong(4,tStartDate);
					pstmt.setLong(5,tEndDate);

					System.out.println(query.toString());

					System.out.println("####### cfiAccount.getExternalKey() = "+ cfiAccount.getExternalKey());
					System.out.println("####### tStartDate = " + tStartDate);
					System.out.println("####### tEndDate = " + tEndDate);

					rs   = pstmt.executeQuery();

					while(rs.next()){
						CorpStatement corpStmt = new CorpStatement();
						corpStmt.setInstanceId(rs.getLong(1));
						corpStmt.setStmtDate(rs.getString(2));
						corpStmt.setAmtDue(rs.getString(3));
						corpStmt.setDueDate(rs.getString(4));
						corpStmt.setReferenceNo(rs.getString(5));
						corpStmt.setStatementRunId(rs.getString(6));
						System.out.println("Statement run ID is "+corpStmt.getStatementRunId());
						cfiStmts.addElement(corpStmt);
					}

					if (pstmt != null) {
						pstmt.close();
					}
					if(rs!=null){
						rs=null;
					}
				}
            }
        }
        catch (Exception ex) {
            System.out.print("-- EXCEPTION: " + ex.getClass() + " " + ex.getMessage());
        }
        catch (Throwable t) {
            System.out.print("-- EXCEPTION: " + t.getClass() + " " + t.getMessage());
        }
        finally {
			if(connection!=null){
				try {
            	    freeDbConnection(connection);
				}
				catch (Exception dbe) {
					System.out.println("Error : " + dbe);
				}
            }
			if(rs!=null){
				rs=null;
			}
        }
        return cfiStmts;
    }

    /** Retrieves the account's statements by date range*/
    public static CorpStatement getCorpStatementById(String strStatementId){
        Vector cfiStmts = new Vector();
		CorpStatement corpStmt = new CorpStatement();
        Connection connection = null;
        PreparedStatement pstmt = null;
        ResultSet rs= null;
        StringBuffer query = new StringBuffer();
        if(strStatementId!=null && strStatementId.indexOf(":")>0){
			StringTokenizer token = new StringTokenizer(strStatementId,":");
			strStatementId = token.nextToken();
			strStatementId = token.nextToken();
		}

		System.out.println("************** strStatementId is "+strStatementId);
        long stmtid = Long.parseLong(strStatementId);

        try {
			connection = getDbConnection();
	            if (connection!=null){

					query.append("select s.instanceid,to_char(to_date(s.statementdate,'J'),'dd/mm/yyyy'),s.userfield2,s.userfield3,s.userfield4 from statement s ");
					query.append("where s.id = ckfrconvert1.INSTANCE_TO_OBJECTID(?,10) ");

					// prepare the statement and bind parameter
					pstmt = connection.prepareStatement(query.toString());
					pstmt.setLong(1,stmtid);

					System.out.println(query.toString());

					rs   = pstmt.executeQuery();

					while(rs.next()){
						corpStmt.setInstanceId(rs.getLong(1));
						corpStmt.setStmtDate(rs.getString(2));
						corpStmt.setAmtDue(rs.getString(3));
						corpStmt.setDueDate(rs.getString(4));
						corpStmt.setReferenceNo(rs.getString(5));
					}

					if (pstmt != null) {
						pstmt.close();
					}
					if(rs!=null){
						rs=null;
					}
				}
        }
        catch (Exception ex) {
            System.out.print("-- EXCEPTION: " + ex.getClass() + " " + ex.getMessage());
        }
        catch (Throwable t) {
            System.out.print("-- EXCEPTION: " + t.getClass() + " " + t.getMessage());
        }
        finally {
			if(connection!=null){
				try {
            	    freeDbConnection(connection);
				}
				catch (Exception dbe) {
					System.out.println("Error : " + dbe);
				}
            }
			if(rs!=null){
				rs=null;
			}
        }
        return corpStmt;
    }

    /** Retrieves the latest statement */
    static String[] sort(Vector cfiStatements){
        String[] sKeys = null;
        try {
            if (cfiStatements!=null){
                int size = cfiStatements.size();

                if (size>0){
                    //System.out.println("Values will be sorted");

                    // Establish the date formats to use
                    final SimpleDateFormat formatter = new SimpleDateFormat("yyyyMMdd");

                    sKeys = new String[size];
                    for (int index=0;index<size;index++) {
                        final Statement cfiStatement = (Statement) cfiStatements.elementAt(index);
                        final String element = EbppxUtil.htmlEncode(formatter.format(cfiStatement.getDate())) + String.valueOf(index);
                        sKeys[index] = element;
                    }
                    Arrays.sort(sKeys, Collections.reverseOrder());
                    //System.out.println("Values have been sorted");
                }
            }
        }
        catch (Exception ex) {
            System.out.print("-- EXCEPTION: " + ex.getClass() + " " + ex.getMessage());
        }
        catch (Throwable t) {
            System.out.print("-- EXCEPTION: " + t.getClass() + " " + t.getMessage());
        }
        finally {
            return sKeys;
        }
    }
}
